---
sidebar_label: Data validations
sidebar_position: 7
description: Data validations in Hasura over BigQuery
keywords:
  - hasura
  - docs
  - bigquery
  - schema
  - data validation
---

import GraphiQLIDE from '@site/src/components/GraphiQLIDE';
import Thumbnail from '@site/src/components/Thumbnail';
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

# BigQuery: Data Validations

## Introduction

Many times, we need to perform validations of input data before selecting, inserting or updating objects.

BigQuery does not natively support constraints, primary or foreign keys.

However, if you would like the validation logic to be at the GraphQL API layer, Hasura permissions can be used to add
your validation.

These solutions are explained in some more detail below.

## Using Hasura permissions

If the validation logic can be expressed **declaratively** using static values and data from the database, then you can
use [row level permissions](/auth/authorization/permissions/row-level-permissions.mdx) to perform the validations.
(Read more about [Authorization](/auth/authorization/index.mdx)).

**Example 1:** Validate that the `editor` role can only select `article`s with empty titles.

Suppose, we have the following tables in our schema:

```sql
authors (id int, name text)
articles (id int, title text, author_id int, body text)
```

Now, we can create a role `editor` and add a select validation rule as follows:

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

<Thumbnail src="/img/schema/bq-validation-not-empty.png" />

</TabItem>
<TabItem value="cli" label="CLI">

You can add roles and permissions in the `tables.yaml` file inside the `metadata` directory:

```yaml {4-10}
table:
  dataset: bigquery
  name: articles
select_permissions:
  - role: editor
    permission:
      columns: '*'
      filter:
        title:
          _eq: ''
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

You can add an select permission rule by using the
[bigquery_create_select_permission](/api-reference/metadata-api/permission.mdx) Metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "bigquery_create_select_permission",
  "args": {
    "source": "<db_name>",
    "table": {
      "name": "articles",
      "dataset": "bigquery"
    },
    "role": "editor",
    "permission": {
      "filter": {
        "title": {
          "_eq": ""
        }
      }
    }
  }
}
```

</TabItem>
</Tabs>

If we set the `X-Hasura-Role` to `editor` and try to select all articles, only those with empty string titles will be
returned:

<GraphiQLIDE
  query={`query {
  bigquery_articles {
    body
    title
    author_id
  }
}
`}
  response={`{
  "data": {
    "bigquery_articles": [
      {
        "body": "Sample article content 1",
        "title": "",
        "author_id": "1"
      }
    ]
  }
}`}
/>

**Example 2:** Validate that `author` roles can only select an `article` if the `author_id` matches `X-Hasura-User-Id`

Suppose, we have 2 tables:

```sql
authors (id int, name text)
articles (id int, title text, author_id int, body text)
```

Also, suppose there is an [object relationship](schema/bigquery/table-relationships/index.mdx) `articles.author` defined
as:

```sql
articles.author_id -> authors.id
```

Now, we can create a role `author` and add an select validation rule as follows:

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

<Thumbnail src="/img/schema/bq-author-own-articles.png" />

</TabItem>
<TabItem value="cli" label="CLI">

You can add roles and permissions in the `tables.yaml` file inside the `metadata` directory:

```yaml {4-10}
table:
  dataset: bigquery
  name: articles
select_permissions:
  - role: author
    permission:
      columns: '*'
      filter:
        author_id:
          _eq: X-Hasura-User-Id
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

You can add a select permission rule by using the
[bigquery_create_select_permission](/api-reference/metadata-api/permission.mdx) Metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "bigquery_create_select_permission",
  "args": {
    "source": "<db_name>",
    "table": {
      "name": "articles",
      "dataset": "bigquery"
    },
    "role": "author",
    "permission": {
      "filter": {
        "author_id": {
          "_eq": "X-Hasura-User-Id"
        }
      }
    }
  }
}
```

</TabItem>
</Tabs>

If we set the `X-Hasura-Role` and `X-Hasura-User-Id` headers, then try to select all articles, only those where the
`author_id` matches the current `X-Hasura-User-Id` will be returned:

<GraphiQLIDE
  query={`query {
    bigquery_articles {
      id
      title
      author_id
    }
  }`}
  response={`{
  "data": {
    "bigquery_articles": [
      {
        "id": "2",
        "title": "veniam",
        "author_id": "1"
      },
      {
        "id": "5",
        "title": "",
        "author_id": "1"
      }
    ]
  }
}`}
/>

:::info Note

Permissions are scoped to a user's role. So, if a validation check needs to be global then you will have to define it
for all roles which have insert/update permissions.

:::
